package com.zufangbao.earth.service;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.junit.Assert;
import org.junit.FixMethodOrder;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.junit.runners.MethodSorters;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.jdbc.Sql;
import org.springframework.test.context.junit4.AbstractTransactionalJUnit4SpringContextTests;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.demo2do.core.persistence.GenericDaoSupport;
import com.demo2do.core.persistence.support.Filter;
import com.demo2do.core.utils.DateUtils;
import com.zufangbao.earth.email.excel.ExcelOverDueOrderExcelHandler;
import com.zufangbao.sun.entity.finance.ReportDataGenerator;
import com.zufangbao.sun.entity.order.Order;
import com.zufangbao.sun.entity.order.OrderStatus;
import com.zufangbao.sun.entity.order.OverDueReportTitles;
import com.zufangbao.sun.service.OrderService;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {
		"classpath:/context/applicationContext-*.xml",
		"classpath:/local/applicationContext-*.xml"})
@FixMethodOrder(MethodSorters.NAME_ASCENDING)
public class VerifyEmailOverDueOrderExcel extends AbstractTransactionalJUnit4SpringContextTests{
	@Autowired
	private OrderService orderService;
	@Value("#{mail['excel.location']}")
	private String filePath;
	@Resource
	private GenericDaoSupport genericDaoSupport;
	@Resource
	private ExcelOverDueOrderExcelHandler excelOverDueOrderExcelHandler;
	
	private POIFSFileSystem fs;
	private HSSFWorkbook wb;
	private HSSFSheet sheet;
	private HSSFRow row;

	/**
	 * 读取Excel表格表头的内容
	 * 
	 * @param InputStream
	 * @return String 表头内容的数组
	 */
	public String[] readExcelTitle(String filePath) {
		try {
			InputStream is = new FileInputStream(filePath);
			fs = new POIFSFileSystem(is);
			wb = new HSSFWorkbook(fs);
		} catch (IOException e) {
			e.printStackTrace();
		}
		sheet = wb.getSheetAt(0);
		row = sheet.getRow(0);
		// 标题总列数
		int colNum = row.getPhysicalNumberOfCells();
		System.out.println("colNum:" + colNum);
		String[] title = new String[colNum];
		for (int i = 0; i < colNum; i++) {
			// title[i] = getStringCellValue(row.getCell((short) i));
			title[i] = getCellFormatValue(row.getCell((short) i));
		}
		return title;
	}

	/**
	 * 读取Excel数据内容
	 * 
	 * @param InputStream
	 * @return Map 包含单元格数据内容的Map对象
	 */
	public Map<Integer, String> readExcelContent(String filePath) {
		Map<Integer, String> content = new HashMap<Integer, String>();
		String str = "";
		try {
			InputStream is = new FileInputStream(filePath);
			fs = new POIFSFileSystem(is);
			wb = new HSSFWorkbook(fs);
		} catch (IOException e) {
			e.printStackTrace();
		}
		sheet = wb.getSheetAt(0);
		// 得到总行数
		int rowNum = sheet.getLastRowNum();
		row = sheet.getRow(0);
		int colNum = row.getPhysicalNumberOfCells();
		// 正文内容应该从第二行开始,第一行为表头的标题
		for (int i = 1; i <= rowNum; i++) {
			row = sheet.getRow(i);
			int j = 0;
			while (j < colNum) {
				// 每个单元格的数据内容用"-"分割开，以后需要时用String类的replace()方法还原数据
				// 也可以将每个单元格的数据设置到一个javabean的属性中，此时需要新建一个javabean
				// str += getStringCellValue(row.getCell((short) j)).trim() +
				// "-";
				str += getCellFormatValue(row.getCell((short) j)).trim()
						+ "----";
				j++;
			}
			content.put(i, str);
			str = "";
		}
		return content;
	}

	/**
	 * 获取单元格数据内容为字符串类型的数据
	 * 
	 * @param cell
	 *            Excel单元格
	 * @return String 单元格数据内容
	 */
	private String getStringCellValue(HSSFCell cell) {
		String strCell = "";
		switch (cell.getCellType()) {
		case HSSFCell.CELL_TYPE_STRING:
			strCell = cell.getStringCellValue();
			break;
		case HSSFCell.CELL_TYPE_NUMERIC:
			strCell = String.valueOf(cell.getNumericCellValue());
			break;
		case HSSFCell.CELL_TYPE_BOOLEAN:
			strCell = String.valueOf(cell.getBooleanCellValue());
			break;
		case HSSFCell.CELL_TYPE_BLANK:
			strCell = "";
			break;
		default:
			strCell = "";
			break;
		}
		if (strCell.equals("") || strCell == null) {
			return "";
		}
		if (cell == null) {
			return "";
		}
		return strCell;
	}

	/**
	 * 获取单元格数据内容为日期类型的数据
	 * 
	 * @param cell
	 *            Excel单元格
	 * @return String 单元格数据内容
	 */
	private String getDateCellValue(HSSFCell cell) {
		String result = "";
		try {
			int cellType = cell.getCellType();
			if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
				Date date = cell.getDateCellValue();
				result = (date.getYear() + 1900) + "-" + (date.getMonth() + 1)
						+ "-" + date.getDate();
			} else if (cellType == HSSFCell.CELL_TYPE_STRING) {
				String date = getStringCellValue(cell);
				result = date.replaceAll("[年月]", "-").replace("日", "").trim();
			} else if (cellType == HSSFCell.CELL_TYPE_BLANK) {
				result = "";
			}
		} catch (Exception e) {
			System.out.println("日期格式不正确!");
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * 根据HSSFCell类型设置数据
	 * 
	 * @param cell
	 * @return
	 */
	private String getCellFormatValue(HSSFCell cell) {
		String cellvalue = "";
		if (cell != null) {
			// 判断当前Cell的Type
			switch (cell.getCellType()) {
			// 如果当前Cell的Type为NUMERIC
			case HSSFCell.CELL_TYPE_NUMERIC:
			case HSSFCell.CELL_TYPE_FORMULA: {
				// 判断当前的cell是否为Date
				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					// 如果是Date类型则，转化为Data格式

					// 方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00
					// cellvalue = cell.getDateCellValue().toLocaleString();

					// 方法2：这样子的data格式是不带带时分秒的：2011-10-12
					Date date = cell.getDateCellValue();
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					cellvalue = sdf.format(date);

				}
				// 如果是纯数字
				else {
					// 取得当前Cell的数值
					cellvalue = String.valueOf(cell.getNumericCellValue());
				}
				break;
			}
			// 如果当前Cell的Type为STRIN
			case HSSFCell.CELL_TYPE_STRING:
				// 取得当前的Cell字符串
				cellvalue = cell.getRichStringCellValue().getString();
				break;
			// 默认的Cell值
			default:
				cellvalue = " ";
			}
		} else {
			cellvalue = "";
		}
		return cellvalue;

	}

	@Test
	@Sql("classpath:test/createTodayBackAccountTableTestData.sql")
	public void CcheckOverDueOrderResultTest() {
		String[] title = readExcelTitle(filePath + "YOPARK 催收清单_"+ "20150403"+".v1.xls");
		int k = OverDueReportTitles.values().length;
		String resultSet[] = new String[k];
		for (OverDueReportTitles key : OverDueReportTitles.values()) {
			resultSet[key.ordinal()] = key.getKey();
		}
		Assert.assertEquals(resultSet, title);
		// 对读取Excel表格内容测试
		Map<Integer, String> map = readExcelContent(filePath
				+ "YOPARK 催收清单_" + "20150403"
				+ ".v1.xls");
		StringBuilder sb = new StringBuilder();
		List<Order> overDueOrderList = query_overDue_return_money_order_list(
				DateUtils.parseDate("2015-04-03", "yyyy-MM-dd"), 2L);
		List<ReportDataGenerator> overDueDataReport = new ArrayList<ReportDataGenerator>();
		for (Order order : overDueOrderList) {
			order.setOverDueDays(DateUtils.getIntervalDays(order.getDueDate(), new Date()).size()-1);
			overDueDataReport.add(order);
		}
		Map<Integer, String> mapOrderNo = new HashMap<Integer, String>();
		for (int i = 0; i < overDueOrderList.size(); i++) {
			sb.append(overDueOrderList.get(i).getOrderNo()).append("----");// gather
		}
		StringBuilder sbTime = new StringBuilder();
		for(int i = 0; i < overDueOrderList.size(); i++){
			sbTime.append(overDueOrderList.get(i).getOverDueDays()).append("TTTT");
		}
		System.out.println("获得Excel表格的内容:");
		int j = 0;
		StringBuffer sf = new StringBuffer();
		for (int i = 1; i <= map.size() - 1; i++) {
			String rowContent = map.get(i);
			if (rowContent.contains("--------")) {// validate if there is empty
													// cell
				Assert.fail();
			}
			String rowColumValue[] = rowContent.split("----");
			if (sb.toString().contains(rowColumValue[1])) {// validate if the
															// result is correct
				j++;
			}
			sf.append(rowColumValue[10]).append("TTTT");
		}
		Assert.assertEquals(sbTime.toString(), sf.toString());
		Assert.assertEquals(overDueOrderList.size(), j);//
		int m = 1;
		double n = 1.0;
		for (ReportDataGenerator reportDataGenerator : overDueDataReport) {
			StringBuilder newsb = new StringBuilder();
			Map<String, String> ResultMap = reportDataGenerator.toReportData();
			for (OverDueReportTitles key : OverDueReportTitles.values()) {
				if (key.getKey().equals(OverDueReportTitles.NO.getKey())) {
					newsb.append(n).append("----");
				} else {
					newsb.append(ResultMap.get(key.getKey())).append("----");
				}
			}
			mapOrderNo.put(m, newsb.toString());
			m++;
			n++;
		}
		map.remove(map.size());
		Assert.assertEquals(map, mapOrderNo);
		new File(filePath
				+ "YOPARK 催收清单_" + "20150403"
				+ ".v1.xls").delete();
	}

	private List<Order> query_overDue_return_money_order_list(Date date,Long appId) {
		Filter filter = new Filter();
		filter.addEquals("orderStatus", OrderStatus.fromValue(0));
		filter.addEquals("contract.app.id", appId);
		Date endDate = DateUtils.addDays(
				date, 1);
		filter.addLessThan("dueDate", endDate);
		com.demo2do.core.persistence.support.Order orderByDueDate = new com.demo2do.core.persistence.support.Order();
		orderByDueDate.add("due_date", "ASC");
		List<Order> orderList = orderService.list(Order.class, filter,
				orderByDueDate);
		return orderList;
	}
	
	@Test
	@Sql("classpath:test/createTodayBackAccountTableTestData.sql")
	public void AcreateWorkBook() throws IOException, ParseException {
		
		List<Order> list = query_overDue_return_money_order_list("2015-04-03");
		BigDecimal financeMoney = BigDecimal.ZERO;
		List<ReportDataGenerator> overDueOrderMap = new ArrayList<ReportDataGenerator>();
		if(CollectionUtils.isNotEmpty(list)){
			for(Order order:list){
				financeMoney = financeMoney.add(order.getTotalRent());
				order.setOverDueDays(DateUtils.getIntervalDays(order.getDueDate(), new Date()).size()-1);
				overDueOrderMap.add(order);
			}
			this.excelOverDueOrderExcelHandler.createWorkBook(overDueOrderMap,financeMoney,OverDueReportTitles.values(),DateUtils.parseDate("2015-04-03", "yyyy-MM-dd"), "YOPARK");
		}
	}


	private List<Order> query_overDue_return_money_order_list(String date) {
		Filter filter = new Filter();
		filter.addEquals("orderStatus", OrderStatus.fromValue(0));
		filter.addEquals("contract.app.id", 2L);
		Date endDate = DateUtils.addDays(
				DateUtils.parseDate(date, "yyyy-MM-dd"), 1);
		filter.addLessThan("dueDate", endDate);
		com.demo2do.core.persistence.support.Order orderByDueDate = new com.demo2do.core.persistence.support.Order();
		orderByDueDate.add("due_date", "ASC");
		List<Order> orderList = orderService.list(Order.class, filter,
				orderByDueDate);
		for (Order order : orderList) {
			order.setOverDueDays(DateUtils.getIntervalDays(order.getDueDate(), new Date()).size()-1);
		}
		return orderList;
	}

	@Test
	@Sql("classpath:test/createTodayBackAccountTableTestData.sql")
	public void Bquery_today_return_money_order_list() throws ParseException {
		String date = "2015-04-03";
		List<Order> orderList = query_overDue_return_money_order_list(date);
		Assert.assertNotNull(orderList);
		Assert.assertEquals(2,orderList.size());
		Order order = orderList.get(0);
		Assert.assertEquals(new Long(107),order.getId());
	}

}